Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Queries via String Concatenation desired instead of Prepared Stmt b/c of DB Index

  1. #1
    Join Date
    May 2008
    Posts
    7

    Default Queries via String Concatenation desired instead of Prepared Stmt b/c of DB Index

    Is there by any chance a hidden "feature" (hack really) or maybe an alternative way of forcing queries to execute via string concatenation instead of prepared statements?

    We're working with an Oracle database in need of much re-org and normalization, but it won't get that treatment for a while. In the meantime, we tread carefully with our query executions. One particularly frustrating issue is that certain queries report explain plans with reasonable numbers if you use a literal, but parameterizing the query confuses the database, resulting in an a significantly increased cost, in this case by 5 orders of magnitude. Unbelievably, this includes when you use the primary key!

    I'll double-check to see if the actual execution plan is that bad (in which case the explain plan would be just a red herring and this post is pointless). Assuming it is not though, I suppose I'm looking for:
    a) a check-box or something along those lines to allow the steps that execute queries (like the database join in this case) to use good-ol'-bad-practice string concatenation instead of prepared statements
    OR b) something along the lines of dynamic SQL

    Thanks for PDI!

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Mmm, did you try to force the use of indexes with Oracle hints? That often helps.

    Dynamic SQL is possible too, but only through variables at the moment. We're thinking about extending it for field values (Dynamic SQL) too, but there is very little demand for it so far.

    Cheers,
    Matt

  3. #3
    Join Date
    May 2008
    Posts
    7

    Default

    I probably don't know my Oracle hints well enough, but that's a good idea that I'll look into. I was just able to hack away at the statement in question and get it to behave decently by adding a "rownum < X" predicate. Using the primary key in the where clause, the rownum predicate, an order by clause, and repeating the transformation on cron, I should be good to go.

    RE: demand, this is basically a hacker approach and certainly not recommended, but sometimes our scale forces us into a corner.

    Thanks again.

  4. #4
    Join Date
    Oct 2007
    Posts
    255

    Default asdf

    I'd have a use for completely dynamic queries, but my use case is not a common one.

    re: hacking, the only difference between a hack and a feature is whether someone decides it's an appropriate solution. Dynamic queries wouldn't be possible if it wasn't a legitimate solution

    -Brian

  5. #5
    Join Date
    May 2006
    Posts
    4,882

    Default

    Even if you had completely dynamic queries you would not be able to change the select part, else the rest of the PDI would not work that well anymore

    And on Oracle you only use prepared statements, in the last 10 years I only found 1 instance where non-prepared statements where better in Oracle (which was on a 5 page sql query which was only executed once per night).

    Regards,
    Sven

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.