?
Solved

Oracle query very slow

Posted on 2013-10-23
5
Medium Priority
?
708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this article, we’ll look at how to deploy ProxySQL.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

765 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