Solved

Oracle query very slow

Posted on 2013-10-23
5
636 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 73

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 73

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 34

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now