Oracle seems to ignore all the hints I give it (using sqldeveloper)

Question

I just wrote out my entire questions and for some reason all the content just randomly deleted, so here we go with a super summed up version.

I am very new to hints and have been taught very little about it, I have done a lot of reading online and watching of Youtube videos but I do not find one that has sufficient examples and visualization that helps me so I apologies if I am just doing it wrong.

So these are the hints I have tried (I tried USE_NL, USE_NL_WITH_INDEX and LEADING but then noticed the CBO automatically used those hints which was good):

    /*+ USE_MERGE(sale_saracl CUSTOMER_saracl) */ 

With this query:

    SELECT AVG(s.sellprice), s.qty, s.custid  
    FROM CUSTOMER_saracl c, sale_saracl s
    WHERE c.custid = s.custid 
    GROUP BY (s.qty,  s.custid) 
    HAVING AVG(s.sellprice) > (
      SELECT MIN(AVG(price)) 
      FROM  product_saracl 
      WHERE pname LIKE 'FA%'
      GROUP BY price
    );

And these are the tables:

    CREATE TABLE CUSTOMER_SARACL( 
    
        CustID         NUMBER(8) NOT NULL,
        FIRST_NAME     VARCHAR2(15),
        SURNAME     VARCHAR2(15),
        ADDRESS     VARCHAR2(20),
        PHONE_NUMBER NUMBER(12)) 
    
        CLUSTER CUST_SALESARACL(CustID); 
    
    CREATE TABLE PRODUCT_SARAHCL( 
    
        ProdID     NUMBER(10) NOT NULL,
        PName    Varchar2(6),
        PDesc    Varchar2(15),
        Price    Number(8),
        QOH        Number(5))
    
    CREATE TABLE SALE_SARACL( 
    
        SaleID     NUMBER(10) NOT NULL,
        SaleDate    DATE,
        Qty            Number(5),
        SellPrice    Number(10),
        CustID        NUMBER(8),
        ProdID        NUMBER(10)) 
    
        CLUSTER CUST_SALESARACL(CustID); 

The index I am using:

    CREATE INDEX customer_index ON CUSTOMER_saracl(custid);

When looking at the execution's plan, it does not change one bit when adding any of the hints and that is the issue I am having.

The plan for the query using the USE_MERGE hint:

Here is the pastebin of the autotrace results on the query with the use merge hint: https://pastebin.pl/view/b72a9a9d

Thank you everyone!

Answer 1

The "join" is unnecessary unless custid is used for filtering (which I doubt). You might get a better execution plan with:

SELECT AVG(s.sellprice), s.qty, s.custid  
FROM sale_saracl s
GROUP BY (s.qty,  s.custid) 
HAVING AVG(s.sellprice) > (SELECT MIN(AVG(ps.price)) 
                           FROM product_saracl ps
                           WHERE ps.pname LIKE 'FA%'
                           GROUP BY price
                          );