Solved

Oracle query very slow

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 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