Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow NULLS LAST in CREATE INDEX [CORE5914] #6172

Closed
firebird-automations opened this issue Sep 18, 2018 · 8 comments
Closed

Allow NULLS LAST in CREATE INDEX [CORE5914] #6172

firebird-automations opened this issue Sep 18, 2018 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Duplicates CORE4266

Add posiibility to specify NULLS LAST/NULLS FIRST directive in index creation

CREATE ASCENDING INDEX IXA_TABLE_FIELD ON TABLE(FIELD) NULLS LAST;

this will help in queries like this

SELECT
*
FROM
TABLE
ORDER BY FIELD NULLS LAST

or

SELECT
*
FROM
TABLE
WHERE
FIELD IS NOT NULL
ORDER BY FIELD

now plan looks like
PLAN (TABLE ORDER IXA_TABLE_FIELD)
which is not efficient because first in index are nulls
But if table have most of nulls but for e.g. 1% contain data, this plan is inefficient

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: Add posiibility to specify NULLS LAST/NULLS FIRS directive in index creation

CREATE ASCENDING INDEX IXA_TABLE_FIELD ON TABLE(FIELD) NULLS LAST;

this will help in queries like this

SELECT
*
FROM
TABLE
ORDER BY FIELD NULLS LAST

or

SELECT
*
FROM
TABLE
WHERE
FIELD IS NOT NULL
ORDER BY FIELD

now plan looks like
PLAN (TABLE ORDER IXA_TABLE_FIELD)
which is not efficient because first in index are nulls
But if table have most of nulls but for e.g. 1% contain data, this plan is inefficient

=>

Add posiibility to specify NULLS LAST/NULLS FIRST directive in index creation

CREATE ASCENDING INDEX IXA_TABLE_FIELD ON TABLE(FIELD) NULLS LAST;

this will help in queries like this

SELECT
*
FROM
TABLE
ORDER BY FIELD NULLS LAST

or

SELECT
*
FROM
TABLE
WHERE
FIELD IS NOT NULL
ORDER BY FIELD

now plan looks like
PLAN (TABLE ORDER IXA_TABLE_FIELD)
which is not efficient because first in index are nulls
But if table have most of nulls but for e.g. 1% contain data, this plan is inefficient

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Please confirm, what is the PLAN for the first query?

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Natural.

But i have thinked about the ticket and including this into index definition will not be relevant.
Better will be, if Firebird can jump NULL index nodes and go throught the rest of the index and then back to NULL node?

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

CORE4266

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

@attila Molnár - you have right, this is duplicate.

And I must have sclerosis. Because i actively participated in that thread from five years ago.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue duplicates CORE4266 [ CORE4266 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant