[
https://issues.apache.org/jira/browse/PHOENIX-4725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16493933#comment-16493933
]
Shubham commented on PHOENIX-4725:
----------------------------------
In oracle, query is able to utilize the INDEX specified in HINT even if we do
not explicitly mention schema with table name. Connection url has schema name
in it.
{code:java}
CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
insert into customers values (1,'cust1', 'CF');
insert into customers values (2,'cust2', 'CF1');
insert into customers values (3,'cust3', 'CF2');
create index customers_idx on customers (city);
set autotrace on exp
SELECT /*+ INDEX_DESC(customers customers_idx) */ *
FROM customers where city = 'CF';
select * from customers;
Autotrace Enabled
Displays the execution plan only.
>>Query Run In:Query Result
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1756912800
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 1 | 67 |
2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING | CUSTOMERS_IDX | 1 | |
1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("CITY"='CF')
filter("CITY"='CF')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
{code}
> Hint part in a query does not take into account the "schema" which is passed
> in the JDBC connection string
> ----------------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-4725
> URL: https://issues.apache.org/jira/browse/PHOENIX-4725
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.7.0
> Reporter: Pulkit Bhardwaj
> Assignee: Shubham
> Priority: Major
>
> If I connect to hbase via phoenix using a jdbc connection and specify the
> schema name in the connection, I would be able to do use the table names
> without having to specify the schema name in the query
> e.g
> {code:java}
> SELECT * from SCHEMA_NAME.TABLE_NAME{code}
> can we written as
> {code:java}
> SELECT * from TABLE_NAME{code}
> but
> let's say I want to pass a hint to use a particular index on the table
> {code:java}
> SELECT /*+ INDEX(SCHEMA_NAME.TABLE_NAME IDX_TABLE) */ * from TABLE_NAME{code}
> the above works, but if I remove the SCHEMA_NAME from inside the hint part,
> the query would not reconise the index
> in other words, the below would not work
> {code:java}
> SELECT /*+ INDEX(TABLE_NAME IDX_TABLE) */ * from TABLE_NAME{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
