[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle query very slow

Posted on 2013-10-23
5
Medium Priority
?
719 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 14

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 1200 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 800 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

872 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