Solved

Oracle query very slow

Posted on 2013-10-23
5
678 Views
Last Modified: 2013-11-20
Hi,
I've this table:

create table test_key
(val1 varchar2(12),
val2 varchar2(12),
kye_num number,
key_num_new number);

Open in new window

with 30.000.000 of rows, below some values:

Insert into TEST_KEY  (VAL1, VAL2, KYE_NUM) Values   ('A', 'B', 1);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('A', 'C', 1);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('A', 'D', 1);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('A', 'B', 2);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('A', 'B', 3);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('C', 'D', 2);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('C', 'D', 1);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('D', 'E', 2);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('D', 'E', 3);
Insert into TEST_KEY   (VAL1, VAL2, KYE_NUM) Values   ('D', 'E', 1);

commit;

Open in new window

I need write a sql query to generate the values into key_num_new like this:

SELECT    val1,val2,kye_num, NVL2 (test_key.val2,
                (SUM (CASE test_key.kye_num WHEN 1 THEN 1 ELSE 0 END)
                 OVER (ORDER BY test_key.val1,val2)),
               0
              ) AS key_num_new
from test_key
order by 1,2

Open in new window


but this query is very slow, Can someone tell me how can I rewrite this query with some function more efficiently?
0
Comment
Question by:ralph_rea
5 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39593592
Considerung the update itself I suggest the use of CTAS with nologging option (and parallel if possible):
1. create table staging_tab as select .... from test_key tablespace <?!?> nologging ....
2. create constraints, indexes etc...
3a. if possible drop test_key table, then rename staging_tab to test_key
3b. if dropping is not possible, you may use dbms_redefinition for online redef of your tables
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 39593876
SELECT val1,
         val2,
         kye_num,
         DENSE_RANK() OVER (ORDER BY val1, val2) key_num_new
    FROM test_key
ORDER BY 1, 2;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39593908
you might want to create a table of the distinct values and new key.
then use that to update your table.

CREATE TABLE temp_new_key AS
SELECT val1, val2, ROWNUM rn
  FROM (  SELECT DISTINCT val1, val2
            FROM test_key
        ORDER BY val1, val2);

UPDATE test_key a
   SET key_num_new =
           (SELECT rn
              FROM temp_new_key b
             WHERE a.val1 = b.val1 AND a.val2 = b.val2);

creating temp_new_key as an index organized table with pk (val1,val2) would help as well

instead of creating the table with CTAS, pre create then insert

CREATE TABLE temp_new_key
(
    val1 VARCHAR2(12),
    val2 VARCHAR2(12),
    rn   NUMBER,
    PRIMARY KEY(val1, val2)
)
ORGANIZATION INDEX;

INSERT INTO temp_new_key
    SELECT val1, val2, ROWNUM rn
      FROM (  SELECT DISTINCT val1, val2
                FROM test_key
            ORDER BY val1, val2);
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 39594010
If you use the suggestion from sdstuber, you can make that significantly faster by using "nologging".  Change the "create table" command to this:

CREATE TABLE temp_new_key
(
    val1 VARCHAR2(12),
    val2 VARCHAR2(12),
    rn   NUMBER,
    PRIMARY KEY(val1, val2)
)
ORGANIZATION INDEX nologging;

(Or, do "alter table temp_new_key nologging;" if you already created it.)

And use the "APPEND" hint in the insert like this:

INSERT /*+APPEND */ INTO temp_new_key
    SELECT val1, val2, ROWNUM rn
      FROM (  SELECT DISTINCT val1, val2
                FROM test_key
            ORDER BY val1, val2);
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 39594228
Looks like question for a school homework.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question