phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shubham (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-4725) Hint part in a query does not take into account the "schema" which is passed in the JDBC connection string
Date Tue, 29 May 2018 17:43:00 GMT

    [ 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)

Mime
View raw message