Home » SQL & PL/SQL » SQL & PL/SQL » Query performance (SQL)
Query performance [message #661389] Thu, 16 March 2017 09:05 Go to next message
Jiggu
Messages: 5
Registered: March 2017
Junior Member
Hi Friends,
Iam very new for this forum. I have one query. Please check the attached query and provide me your valuable suggestions.

In this query RAV.INVO_INVOICENO IN() we are providing 20000 INVO_INVOICENO numbers. performance is very slow..

Kindly check and provide me the suggestions.

Thanks and Regards,
Jiggu
Re: Query performance [message #661390 is a reply to message #661389] Thu, 16 March 2017 09:12 Go to previous messageGo to next message
joy_division
Messages: 4768
Registered: February 2005
Location: East Coast USA
Senior Member
Please post it inline. I do not open attachments.
Re: Query performance [message #661391 is a reply to message #661390] Thu, 16 March 2017 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 25581
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query performance [message #661393 is a reply to message #661391] Thu, 16 March 2017 10:00 Go to previous messageGo to next message
Jiggu
Messages: 5
Registered: March 2017
Junior Member
Hi Joy,
Please check the complete query....

SELECT
CAST(RAV.INVO_NO AS VARCHAR(50)) as Invoice_Number,
RAV.INVO_CLNTNO as Client_Number,
TRIM(RAV.INVO_DEBC_DEBC) as Client_Customer_Reference,
RAV.INVO_DEBC_NO as Debtor_Number,
RAV.INVO_INVDATE as Invoice_Date,
RAV.INVO_CLIENT_DUEDATE as Due_Date,
RAV.INVO_CURRENCY as Currency,
RAV.INVO_AINITIALI as Document_Amount,
CAST(RAV.INVO_MARKCODERISK AS VARCHAR(50)) as Risk_Mark_Code,
CAST(RAV.INVO_COLLECTIONMARKCODE AS VARCHAR(50)) as Collection_Mark_Code,
RAV.INVO_INVOICENO as Document_Number,
RAV.INVO_ADMNO as Admin_Number,
RAV.INVO_DEBH_NO as Heller_Debtor_Number,
RAV.INVO_DISPUTE_YN as Disputed,
DEBH.DEBH_HADR_NAME1 as Heller_Debtor_Name,
RAV.INVO_AACTUALI as Actual_Balance,
CAST(DEBH.DEBH_LEGALOWNER_DEBH AS VARCHAR(50)) as Legal_Owner,
INVA_ACTA_NR_F + INVA_ACTA_NR_NF AS NR_Amount,
INVA_ACTA_R_F + INVA_ACTA_R_NF AS R_Amount,
DT.CODE_DESCRIPTION as Debtor_Type,
CAST(ISFS.ISFS_STATUS AS VARCHAR(50)) as Item_Status,
VIN.EFID_VALUE AS VAT_Invoice_Number,
VID.EFID_VALUE AS VAT_Invoice_Date,
EC.EFID_VALUE AS End_Customer,
CN.EFID_VALUE AS Contract_Number
FROM
RAV
INNER JOIN INVA
ON INVA.INVA_NO = RAV.INVO_NO
AND RAV.INVO_CURRENCY = INVA.INVA_CURRENCY
INNER JOIN DEBH
ON DEBH.DEBH_NO = RAV.INVO_DEBH_NO
LEFT JOIN CWCS DT
ON DT.CODE_NUMBERN = DEBH.DEBH_DEBTORTYPE
AND DT.CODE_TYPE = 135
AND DT.CODE_LANGUAGE = 'GB'
LEFT JOIN ISFS
ON ISFS.ISFS_INVO_NO = RAV.INVO_NO
LEFT JOIN EFID VIN
ON VIN.EFID_INVO_NO = RAV.INVO_NO
AND VIN.EFID_FIELD_NAME = 'VAT INVOICE NUMBER'
LEFT JOIN EFID VID
ON VID.EFID_INVO_NO = RAV.INVO_NO
AND VID.EFID_FIELD_NAME = 'VAT INVOICE DATE'
LEFT JOIN EFID VIA
ON VIA.EFID_INVO_NO = RAV.INVO_NO
AND VIA.EFID_FIELD_NAME = 'VAT INVOICE AMOUNT'
LEFT JOIN EFID EC
ON EC.EFID_INVO_NO = RAV.INVO_NO
AND EC.EFID_FIELD_NAME = 'END CUSTOMER'
LEFT JOIN EFID CN
ON CN.EFID_INVO_NO = RAV.INVO_NO
AND CN.EFID_FIELD_NAME = 'CONTRACT NUMBER'

WHERE
RAV.INVO_AACTUALI > 0
AND RAV.INVO_ACCRUAL <> 'Y'
AND RAV.INVO_MARKCODESPEC <> 943
AND RAV.INVO_MARKCODERISK <> 149
AND (RAV.INVO_CURRENCY = 'USD')
AND RAV.INVO_INVOICENO IN ('24584667',
'24584666','24584665','24584664','24584663','24584662','24584661','24584660','24584659','24584658',
'24584657','24584656','24584655','24584654','24584653','24584652','24584651','24584650','24584649',
'24584648','24584647','24584646','24584645','24584644','24584643')
AND ROWNUM <= 5000 ;
Re: Query performance [message #661394 is a reply to message #661393] Thu, 16 March 2017 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 25581
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Query performance [message #661401 is a reply to message #661393] Thu, 16 March 2017 12:32 Go to previous messageGo to next message
John Watson
Messages: 7031
Registered: January 2010
Location: Global Village
Senior Member
You say that your IN list has 20000 values, but your final predicate requests just 5000 randomly selected rows. Is that right?
Re: Query performance [message #661402 is a reply to message #661389] Thu, 16 March 2017 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 25581
Registered: January 2009
Location: SoCal
Senior Member
Jiggu wrote on Thu, 16 March 2017 07:05
Hi Friends,
Iam very new for this forum. I have one query. Please check the attached query and provide me your valuable suggestions.

In this query RAV.INVO_INVOICENO IN() we are providing 20000 INVO_INVOICENO numbers. performance is very slow..

so how exactly do you manage 20,000 Invoice numbers in your IN list?
[oracle@vbgeneric ~]$ oerr ora 1795
01795, 00000, "maximum number of expressions in a list is 1000"
// *Cause:    Number of expressions in the query exceeded than 1000.
//            Note that unused column/expressions are also counted  
//            Maximum number of expressions that are allowed are 1000.
// *Action:   Reduce the number of expressions in the list and resubmit.
[oracle@vbgeneric ~]$ 
Re: Query performance [message #661475 is a reply to message #661389] Sun, 19 March 2017 19:25 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Adding Indexes to an Application Database is generally a trade off between
Query Performance and the Additional Transaction Overhead/Additional Disk
Storage space for the Indexes. I would suggest talking it over with your team
before implementing and hopefully you have a test environment to try the changes.



Some suggested Indexing Strategy. The RAV Table appears to be the Primary Table
and you should check if the Column invo_invoiceno in the RAV Table is indexed.

CREATE INDEX rav_ak ON rav (invo_invoiceno)
TABLESPACE ???;

Also the EFID Table appears to be the second primary table in the Query (5 Joins)
and is possibly a detail table of the RAV Table. You may consider a composite index
on the columns efid_invo_no, efid_field_name, efid_value.

Also note the addition of the Column VAT_Invoice_Amount to the Query since there is
join to include the data. I also included a possible approach to De-normalizing the
Queries on the EFID Table to use a single join. It is an addition to possibly generate
some additional discussion.

CREATE INDEX efid_ak ON efid (efid_invo_no, efid_field_name, efid_value)
TABLESPACE ???;

I reformatted the Original SQL for Review. Formatted Queries are generally easier to
understand and diagnose.

SELECT CAST(rav.invo_no AS VARCHAR(50))                 AS Invoice_Number, 
       rav.invo_clntno                                  AS Client_Number, 
       TRIM(rav.invo_debc_debc)                         AS Client_Customer_Reference, 
       rav.invo_debc_no                                 AS Debtor_Number, 
       rav.invo_invdate                                 AS Invoice_Date, 
       rav.invo_client_duedate                          AS Due_Date, 
       rav.invo_currency                                AS Currency, 
       rav.invo_ainitiali                               AS Document_Amount, 
       CAST(rav.invo_markcoderisk AS VARCHAR(50))       AS Risk_Mark_Code, 
       CAST(rav.invo_collectionmarkcode AS VARCHAR(50)) AS Collection_Mark_Code, 
       rav.invo_invoiceno                               AS Document_Number, 
       rav.invo_admno                                   AS Admin_Number, 
       rav.invo_debh_no                                 AS Heller_Debtor_Number, 
       rav.invo_dispute_yn                              AS Disputed, 
       debh.debh_hadr_name1                             AS Heller_Debtor_Name, 
       rav.invo_aactuali                                AS Actual_Balance, 
       CAST(debh.debh_legalowner_debh AS VARCHAR(50))   AS Legal_Owner, 
       inva_acta_nr_f + inva_acta_nr_nf                 AS NR_Amount, 
       inva_acta_r_f + inva_acta_r_nf                   AS R_Amount, 
       dt.code_description                              AS Debtor_Type, 
       CAST(isfs.isfs_status AS VARCHAR(50))            AS Item_Status, 
       vin.efid_value                                   AS VAT_Invoice_Number,
       vid.efid_value                                   AS VAT_Invoice_Date, 
       via.efid_value                                   AS VAT_Invoice_Amount, -- Column was missing based on Join Info
       ec.efid_value                                    AS End_Customer, 
       cn.efid_value                                    AS Contract_Number 
FROM   rav                                                                     rav
       INNER JOIN     inva                                                     inva
               ON     inva.inva_no        = rav.invo_no 
                  AND rav.invo_currency   = inva.inva_currency 
       INNER JOIN     debh                                                     debh
               ON     debh.debh_no        = rav.invo_debh_no 
       LEFT JOIN      cwcs                                                     dt 
              ON      dt.code_numbern     = debh.debh_debtortype 
                  AND dt.code_type        = 135 
                  AND dt.code_language    = 'GB' 
       LEFT JOIN      isfs                                                     isfs 
              ON      isfs.isfs_invo_no   = rav.invo_no 
       LEFT JOIN      efid                                                     vin 
              ON      vin.efid_invo_no    = rav.invo_no 
                  AND vin.efid_field_name = 'VAT INVOICE NUMBER' 
       LEFT JOIN      efid                                                     vid 
              ON      vid.efid_invo_no    = rav.invo_no 
                  AND vid.efid_field_name = 'VAT INVOICE DATE' 
       LEFT JOIN      efid                                                     via 
              ON      via.efid_invo_no    = rav.invo_no 
                  AND via.efid_field_name = 'VAT INVOICE AMOUNT' 
       LEFT JOIN      efid                                                     ec 
              ON      ec.efid_invo_no     = rav.invo_no 
                  AND ec.efid_field_name  = 'END CUSTOMER' 
       LEFT JOIN      efid                                                     cn 
              ON      cn.efid_invo_no     = rav.invo_no 
                  AND cn.efid_field_name  = 'CONTRACT NUMBER' 
WHERE  rav.invo_aactuali                  > 0 
AND    rav.invo_accrual                  <> 'Y' 
AND    rav.invo_markcodespec             <> 943 
AND    rav.invo_markcoderisk             <> 149 
AND  ( rav.invo_currency                  = 'USD' ) 
AND    rav.invo_invoiceno                IN ( '24584667', '24584666', '24584665', 
                                              '24584664', '24584663', '24584662', '24584661', 
                                              '24584660', '24584659', '24584658', '24584657', 
                                              '24584656', '24584655', '24584654', '24584653', 
                                              '24584652', '24584651', '24584650', '24584649', 
                                              '24584648', '24584647', '24584646', '24584645', 
                                              '24584644', '24584643' ) 
AND ROWNUM                               <= 5000;



Possible change to de-normalize the 5 separate LEFT JOIN Connections to the EFID Table by
replacing with a de-normalized in-line view using one Join

SELECT  efid.efid_invo_no
       ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE NUMBER' , efid_value, NULL)) VAT_Invoice_Number
       ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE DATE'   , efid_value, NULL)) VAT_Invoice_Date
       ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE AMOUNT' , efid_value, NULL)) VAT_Invoice_Amount
       ,MAX(DECODE efid.efid_field_name, 'END CUSTOMER'       , efid_value, NULL)) End_Customer
       ,MAX(DECODE efid.efid_field_name, 'CONTRACT NUMBER'    , efid_value, NULL)) Contract_Number
FROM   efid
WHERE  efid.efid_field_name IN ( 'VAT INVOICE NUMBER','VAT INVOICE DATE','VAT INVOICE AMOUNT'
                                ,'END CUSTOMER','CONTRACT NUMBER' )
GROUP BY efid.efid_invo_no


Possible modified original query. Index suggestions the same. No guarantees since
I was unable to fully test the mod.

SELECT CAST(rav.invo_no AS VARCHAR(50))                 AS Invoice_Number, 
       rav.invo_clntno                                  AS Client_Number, 
       TRIM(rav.invo_debc_debc)                         AS Client_Customer_Reference, 
       rav.invo_debc_no                                 AS Debtor_Number, 
       rav.invo_invdate                                 AS Invoice_Date, 
       rav.invo_client_duedate                          AS Due_Date, 
       rav.invo_currency                                AS Currency, 
       rav.invo_ainitiali                               AS Document_Amount, 
       CAST(rav.invo_markcoderisk AS VARCHAR(50))       AS Risk_Mark_Code, 
       CAST(rav.invo_collectionmarkcode AS VARCHAR(50)) AS Collection_Mark_Code, 
       rav.invo_invoiceno                               AS Document_Number, 
       rav.invo_admno                                   AS Admin_Number, 
       rav.invo_debh_no                                 AS Heller_Debtor_Number, 
       rav.invo_dispute_yn                              AS Disputed, 
       debh.debh_hadr_name1                             AS Heller_Debtor_Name, 
       rav.invo_aactuali                                AS Actual_Balance, 
       CAST(debh.debh_legalowner_debh AS VARCHAR(50))   AS Legal_Owner, 
       inva_acta_nr_f + inva_acta_nr_nf                 AS NR_Amount, 
       inva_acta_r_f + inva_acta_r_nf                   AS R_Amount, 
       dt.code_description                              AS Debtor_Type, 
       CAST(isfs.isfs_status AS VARCHAR(50))            AS Item_Status, 
       efid.vat_invoice_number                          AS VAT_Invoice_Number,
       efid.vat_invoice_date                            AS VAT_Invoice_Date, 
       efid.vat_invoice_amount                          AS VAT_Invoice_Amount,
       efid.end_customer                                AS End_Customer, 
       efid.contract_number                             AS Contract_Number 
FROM   rav                                                                     rav
       INNER JOIN     inva                                                     inva
               ON     inva.inva_no        = rav.invo_no 
                  AND rav.invo_currency   = inva.inva_currency 
       INNER JOIN     debh                                                     debh
               ON     debh.debh_no        = rav.invo_debh_no 
       LEFT JOIN      cwcs                                                     dt 
              ON      dt.code_numbern     = debh.debh_debtortype 
                  AND dt.code_type        = 135 
                  AND dt.code_language    = 'GB' 
       LEFT JOIN      isfs                                                     isfs 
              ON      isfs.isfs_invo_no   = rav.invo_no
       LEFT JOIN
            ( SELECT  efid.efid_invo_no
                     ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE NUMBER' , efid_value, NULL)) VAT_Invoice_Number
                     ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE DATE'   , efid_value, NULL)) VAT_Invoice_Date
                     ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE AMOUNT' , efid_value, NULL)) VAT_Invoice_Amount
                     ,MAX(DECODE efid.efid_field_name, 'END CUSTOMER'       , efid_value, NULL)) End_Customer
                     ,MAX(DECODE efid.efid_field_name, 'CONTRACT NUMBER'    , efid_value, NULL)) Contract_Number
              FROM    efid
              WHERE   efid.efid_field_name IN ( 'VAT INVOICE NUMBER','VAT INVOICE DATE','VAT INVOICE AMOUNT'
                                               ,'END CUSTOMER','CONTRACT NUMBER' )
              GROUP BY efid.efid_invo_no
            )                                                                  efid
WHERE  rav.invo_aactuali                  > 0 
AND    rav.invo_accrual                  <> 'Y' 
AND    rav.invo_markcodespec             <> 943 
AND    rav.invo_markcoderisk             <> 149 
AND  ( rav.invo_currency                  = 'USD' ) 
AND    rav.invo_invoiceno                IN ( '24584667', '24584666', '24584665', 
                                              '24584664', '24584663', '24584662', '24584661', 
                                              '24584660', '24584659', '24584658', '24584657', 
                                              '24584656', '24584655', '24584654', '24584653', 
                                              '24584652', '24584651', '24584650', '24584649', 
                                              '24584648', '24584647', '24584646', '24584645', 
                                              '24584644', '24584643' ) 
AND ROWNUM                               <= 5000;
Re: Query performance [message #661478 is a reply to message #661475] Mon, 20 March 2017 05:36 Go to previous messageGo to next message
Jiggu
Messages: 5
Registered: March 2017
Junior Member
Hi Thank you..But iam getting the below error while running the query.

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 51 Column: 1
Re: Query performance [message #661488 is a reply to message #661389] Mon, 20 March 2017 14:19 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Sorry about that I Had a Cut and Paste Malfunction and forgot the ON Clause
for line 51. Hope that corrects the error.

Also you my want to add a WHERE Clause with the efid.efid_invo_no assigned to some Invoice Number
for the previous de-normalizing "SELECT efid.efid_invo_no ..." Query



       LEFT JOIN
            ( SELECT  efid.efid_invo_no
                     ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE NUMBER' , efid_value, NULL)) VAT_Invoice_Number
                     ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE DATE'   , efid_value, NULL)) VAT_Invoice_Date
                     ,MAX(DECODE efid.efid_field_name, 'VAT INVOICE AMOUNT' , efid_value, NULL)) VAT_Invoice_Amount
                     ,MAX(DECODE efid.efid_field_name, 'END CUSTOMER'       , efid_value, NULL)) End_Customer
                     ,MAX(DECODE efid.efid_field_name, 'CONTRACT NUMBER'    , efid_value, NULL)) Contract_Number
              FROM    efid
              WHERE   efid.efid_field_name IN ( 'VAT INVOICE NUMBER','VAT INVOICE DATE','VAT INVOICE AMOUNT'
                                               ,'END CUSTOMER','CONTRACT NUMBER' )
              GROUP BY efid.efid_invo_no
            )                                                                  efid
              ON      efid.efid_invo_no   = rav.invo_no

Re: Query performance [message #661508 is a reply to message #661488] Wed, 22 March 2017 03:10 Go to previous messageGo to next message
Jiggu
Messages: 5
Registered: March 2017
Junior Member
Hi, thank you. Now there is no syntax errors.
But I put the 3000 invoices in 'IN' Claus.Getting the below error message. Please help me how to solve.
Example: AND rav.invo_invoiceno IN ( ( '24584667', '24584666', '24584665','24584664', '24584663', '24584662', '24584661',......................up to 3000))

Error message:
ORA-01795: maximum number of expressions in a list is 1000
01795. 00000 - "maximum number of expressions in a list is 1000"
*Cause:
*Action:
Error at Line: 1,061 Column: 1
Re: Query performance [message #661509 is a reply to message #661508] Wed, 22 March 2017 03:28 Go to previous messageGo to next message
John Watson
Messages: 7031
Registered: January 2010
Location: Global Village
Senior Member
Please use [code] tags when you post code or errors, How to use [code] tags and make your code easier to read

You need to put the numbers into a table, and join to it.
Re: Query performance [message #661514 is a reply to message #661509] Wed, 22 March 2017 06:44 Go to previous messageGo to next message
Bill B
Messages: 1663
Registered: December 2004
Senior Member
You may NOT have more then 1000 hardcoded items in an IN list. However if the 5000 or 10000 or any number are in a table then you can simply use

and  rav.invo_invoiceno in (select my_key_word from my_table)

and it works fine.

[Updated on: Wed, 22 March 2017 06:44]

Report message to a moderator

Re: Query performance [message #661518 is a reply to message #661508] Wed, 22 March 2017 08:09 Go to previous messageGo to next message
joy_division
Messages: 4768
Registered: February 2005
Location: East Coast USA
Senior Member
Jiggu wrote on Wed, 22 March 2017 04:10

But I put the 3000 invoices in 'IN' Claus.Getting the below error message. Please help me how to solve.
Example: AND rav.invo_invoiceno IN ( ( '24584667', '24584666', '24584665','24584664', '24584663', '24584662', '24584661',......................up to 3000))

Error message:
ORA-01795: maximum number of expressions in a list is 1000
01795. 00000 - "maximum number of expressions in a list is 1000"
*Cause:
*Action:
Error at Line: 1,061 Column: 1

And this is exactly what Black Swan told you.

Plus, I have a feeling that you are using ROWNUM incorrectly. ROWNUM operates on the complete dataset AFTER it is returned, not before, so if you think you are limiting the query to 5000 rows and then applying all of your conditions, you would be incorrect.
Re: Query performance [message #661524 is a reply to message #661518] Wed, 22 March 2017 09:12 Go to previous messageGo to next message
cookiemonster
Messages: 12828
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not sure why anyone would want to use the rownum limit before applying other conditions - it would make the rownum limit a theoretical maximum.
The rownum use is suspect because the query isn't written as a top-n query so which 5000 rows you get is random.
Re: Query performance [message #661527 is a reply to message #661524] Wed, 22 March 2017 10:03 Go to previous messageGo to next message
Jiggu
Messages: 5
Registered: March 2017
Junior Member
If I add the 3000 invoice numbers in temp table and call the temp table in "IN" clause the query performance is very slow. can some please tell me to pass the 3000 invoices in to that query and performance should not impact..

Thanks and Regards,
Jiggu
Re: Query performance [message #661528 is a reply to message #661527] Wed, 22 March 2017 10:12 Go to previous messageGo to next message
John Watson
Messages: 7031
Registered: January 2010
Location: Global Village
Senior Member
So you need to tune the query, which I would expect to run as a semi join.

The usual process: capture the exec plan, see if you can come up with a better one. An obvious starting point is what you call your "temp table". Does it have statistics? Indexes?
Re: Query performance [message #661529 is a reply to message #661527] Wed, 22 March 2017 10:14 Go to previous messageGo to next message
BlackSwan
Messages: 25581
Registered: January 2009
Location: SoCal
Senior Member
Jiggu wrote on Wed, 22 March 2017 08:03
If I add the 3000 invoice numbers in temp table and call the temp table in "IN" clause the query performance is very slow. can some please tell me to pass the 3000 invoices in to that query and performance should not impact..

Thanks and Regards,
Jiggu
If you need assistance to improve query performance, please do as below

http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query performance [message #661532 is a reply to message #661524] Wed, 22 March 2017 13:29 Go to previous messageGo to next message
joy_division
Messages: 4768
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Wed, 22 March 2017 10:12
Not sure why anyone would want to use the rownum limit before applying other conditions - it would make the rownum limit a theoretical maximum.
The rownum use is suspect because the query isn't written as a top-n query so which 5000 rows you get is random.
Just based on the fact that OP got the same error message as was previously brought to their attention and then asked about it as if they didn't know why they got it, I suspected that using the ROWNUM < 5000 was the OP's intent to speed up the query by using only 5000 rows for their query. Incorrectly assuming that a query against a small dataset would be faster than a query against a large dataset.
Re: Query performance [message #661582 is a reply to message #661532] Fri, 24 March 2017 04:40 Go to previous message
Bill B
Messages: 1663
Registered: December 2004
Senior Member
If the data is in a temp table then you have to add an index on the table or it will be as slow as death. Personally I wouldn't use a standard table with an index. I would put my values into an index organized table that way no index is required.
Previous Topic: oracle transaction
Next Topic: ORACLE sql HELP NEEDED
Goto Forum:
  


Current Time: Wed Aug 23 18:25:59 CDT 2017

Total time taken to generate the page: 0.02025 seconds