Questions tagged [functional-index]
8 questions
6
votes
1 answer
Why would Sybase not use a functional index?
I've created a functional index on a sybase table.
create index acadress_codpost_lower on acadress(LOWER(l5_codpost))
I then run a complex query that uses the index. Without the index it takes 17.086 seconds. With the index it takes 0.076…
Tim B
- 40,716
- 16
- 83
- 128
3
votes
1 answer
Oracle Db 11.2 NLS_SORT Index Range Scan Performance
We use 'nls_sort' functional indexes on 'varchar2' columns. When we try to fetch a unique value, index performance is just as good as ordinary (non-functional) index. But when we try to write a query with a 'range scan' plan on the 'nls_sort'…
Taner Mansur
- 71
- 2
- 5
1
vote
1 answer
Can't create a functional index on top of my custom function
I'm playing with functional index in MySQL 8, and i'm using the *employees* database.
So I created this function:
DELIMITER $$
CREATE FUNCTION salary_range2(salary DECIMAL(10,2))
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN…
luca84
- 13
- 3
1
vote
0 answers
Oracle Functional Index vs Index with Filter Predicates
I have the following query:
SELECT * FROM USERS WHERE UPPER(FIRST_NAME) = 'JOHN' AND UPPER(LAST_NAME) = 'DOE' AND DOB = '07-SEP-11 00:00:00'
Is there any performance benefit to creating a functional index:
CREATE INDEX functional_idx ON USERS…
JonathanSK
- 81
- 2
- 6
0
votes
1 answer
How to make MySQL use functional index on a datetime column?
Say I'm running MySQL 8 with a table data containing about 1M rows. And I want to filter a datetime column on date a range (using a date index).
CREATE TABLE `data` (
`rowId` int NOT NULL AUTO_INCREMENT,
`data` json NOT NULL,
`created`…
ΔO 'delta zero'
- 3,506
- 1
- 19
- 31
0
votes
1 answer
Postgresql not using created functional index
For running the query,
SELECT count(*) FROM reservations WHERE
(((json #>> '{details, attributes, checkIn}')::timestamptz at time zone (json #>> '{details, attributes, destinationTimeZone}'))) >= '2019-01-17' AND (((json #>> '{details, attributes,…
iamcoded
- 1
0
votes
2 answers
Force index to be updated
I'm handing ownerships as "Project -> Ownership -> User" relations and the following function gets the project owners' names as text:
CREATE FUNCTION owners_as_text(projects) RETURNS TEXT AS $$
SELECT trim(both concat_ws(' ', screen_name,…
svoop
- 3,318
- 1
- 23
- 41
0
votes
2 answers
Creating Index on Max Decode function
We have a table which stores user information in attribute name and value pairs. On this table, we have created a view by transposing the rows to columns using decode. MAX(DECODE(attribute_name,'FirstName',attribute_Value)) FirstName
CREATE OR…
Real Chembil
- 261
- 2
- 7
- 23