Re: Optimizing a SQL statement

Hello Andy,
to be honest I haven't read through the whole thread and as I said it is a little bit guess work because of the important bits are missing ... however based on Amir's description "Almost all of the columns in the statement are indexed" and the column names (*_id, *_serial) I think it is unlikely that he gonna hit an ORA-01450.

However maybe he gonna provide the needed information or maybe the issue is already fixed anyway :-)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Andy Sayer <andysayer_at_gmail.com> hat am 6. Dezember 2019 um 11:12 geschrieben:
>
> Stefan,
>
> As I explained before, you’d need every single one of those like filtered columns in your index to allow it to be used to filter rows. Not a single one of the filters can be applied on its own because they are OR’ed together. By the time you’ve added all the columns to the same index, you’ll most likely hit the index key size (which is less than half the block size) or you’ll have an index which is essentially the table and will always need (fast) full scanning.
>  
> One other plan that comes to mind is if OR expansion were to occur. If all columns were individually indexed then this would be possible, but Oracle is still likely to cost that fairly expensively with the bind predicates as there could be leading wildcards. Depending on how the predicates are actually used, this might be okay. If more than one or two include a leading wildcard, you’re probably going to do more work than one full tablescan would.
>  
> I stick with best to start by thinking about rewriting the ORs.
>  
> Thanks,
> Andy
>
>
> On Fri, 6 Dec 2019 at 08:10, Stefan Koehler < contact_at_soocs.de> wrote:
> > Hello Amir,
> > unfortunately one of the most important information (access/filter predicates and column projections) is missing in your case but let's do a little bit of guess work here.
> >
> > > Almost all of the columns in the statement are indexed. Tables PRODUCT and PLACE have 4,576,690 and 1,892,243 rows respectively.
> >
> > Your statement is CPU driven which is possibly caused by the amount of LIOs which are mainly driven by the PRODUCT table access (4.570.888). Your index access/filter predicates are almost returning all rows (4.578.439) which are then additionally filtered down to 1 by one of the predicates by PRODUCT table access.
> >
> > So the question is - how does the access/filter predicates and column projections look like - if you can avoid the PRODUCT table access at all (or filter more early in the index) it should be way faster :)

--
http://www.freelists.org/webpage/oracle-l