Skip to Main Content
  • Questions
  • I NEED YOU HELP WITH THIS QUERY! URGENT.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Hasan.

Asked: February 23, 2019 - 12:21 am UTC

Last updated: February 23, 2019 - 6:51 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

Hello

I need your help, i don't know how to do this, I'm noob in this field and if you can help I really really gonna be happy.

Here is...

Assume that the following query is taking a very long time to run.
The logins table has 10M records, and there's an index on month, day and year. What could be done to speed up the query?

SELECT                 
cast(logins.month || '/' || logins.day || '/' || logins.year as date) as loginDt,
logins.person    
FROM logins      
LEFT JOIN MIN_LUNCH ON MIN_LUNCH.person = logins.person
AND MIN_LUNCH.date = cast(logins.month || '/' || logins.day || '/' || logins.year as date)
WHERE
cast(logins.month || '/' || logins.day || '/' || logins.year as date) between '01/01/2010' and '03/01/2010'

and Connor said...

1... NO NEED TO SHOUT

2...Dates should be stored as *dates* not as 3 columns.

Anyway...try this

SELECT                  
to_date(logins.month || '/' || logins.day || '/' || logins.year,'mm/dd/yyyy') as loginDt,
logins.person     
FROM logins       
LEFT JOIN MIN_LUNCH ON MIN_LUNCH.person = logins.person 
AND MIN_LUNCH.date = to_date(logins.month || '/' || logins.day || '/' || logins.year,'mm/dd/yyyy')
WHERE logins.month between 1 and 3
and   logins.day = 1
and   logins.year = 2010


But rest assured, having dates stored in 3 columns is going to be a nightmare in all sorts of ways.

You might get some benefit by creating a virtual column, eg

alter table logins add a_real_date generated always as ( to_date(logins.month || '/' || logins.day || '/' || logins.year,'mm/dd/yyyy') )

and then index "a_real_date" and write your queries using that column

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library