Sanity check: is this the correct way to get the s...
# suiteqlm
michoel
12/01/2020, 11:05 PMSanity check: is this the correct way to get the subsidiary from a transaction:
SELECT transaction.id, transactionline.subsidiary FROM transaction JOIN transactionline ON transaction.id = transactionline.transaction AND transactionline.mainline = 'T' WHERE ...t
tdietrich
12/02/2020, 3:02 AM@michoel I think it depends on what you're trying to do. If you're only trying to get the subsidiary for given transaction, you could do something like this:
SELECT Subsidiary FROM TransactionLine WHERE Transaction = 16264 AND Mainline = 'T'tdietrich
12/02/2020, 3:04 AMOr you could do something like this:
Copy code
SELECT
Transaction.TranID,
Transaction.TranDate,
Transaction.Type,
( SELECT TransactionLine .Subsidiary FROM TransactionLine WHERE TransactionLine.Transaction =Transaction.ID AND Mainline = 'T' ) AS Subsidiary,
FROM
Transaction
WHERE
Transaction.ID = 16264m
michoel
12/02/2020, 3:04 AM@tdietrich thanks. I need other fields from the transaction table as well, hence the join. I just find it strange that some "body" fields are on the transactionline table
michoel
12/02/2020, 3:07 AMMight your subquery version result in a seperate query per transaction? Or is the query optimiser smart enough to avoid that?
t
tdietrich
12/02/2020, 3:09 AMI don't think they really are on the body. It depends on the transaction type. For example, if you look at an SO in the UI, it appears that the subsidiary is stored at the body level. But I think the values are really stored at the line level to accommodate intercompany journal entries.
👍 1
tdietrich
12/02/2020, 3:09 AMThe correlated subquery is very inefficient, but I hope that the optimizer would work some magic.
tdietrich
12/02/2020, 3:10 AMI wish we had visibility into that sort of thing.
tdietrich
12/02/2020, 3:11 AMAnd yes, it would result in separate subqueries for every row in the main query.
m
michoel
12/02/2020, 3:15 AMYeah like ability to run
EXPLAIN PLAN👍 1
t
tdietrich
12/02/2020, 3:20 AMYes! Can you imagine?
tdietrich
12/02/2020, 3:21 AMAt the very least, I'd like to see a log of long running queries.
m
michoel
12/02/2020, 3:22 AMYour point about the subsidiary being stored on live level makes sense. I guess it's confusing because that's abstracted away working with record API's and saved searches.
t
tdietrich
12/02/2020, 3:23 AMIt would also be nice to know what columns are indexed. Or put another way, I'd like to see what indexes exist on standard and custom tables.
m
michoel
12/02/2020, 3:23 AMFrom a quick test in console the subquery version doesn't seem crazily slower than the join version
t
tdietrich
12/02/2020, 3:27 AMI've noticed that in other queries. I don't know if it's just because Oracle is just brute forcing the queries, or doing something "intelligent" to work around our inefficiencies.
I've started experimenting with things to see if I can optimize queries, based on experience with other RDBMSes. But it's hard to tell if those things make a difference at all, because I have yet to come across a query that runs slowly.
It's a weird environment and situation to be working in, if you know what I mean.
m
michoel
12/02/2020, 3:29 AMI know exactly what you mean
i
Israel
12/02/2020, 4:16 PMReminds me of this comment thread from hackernews - https://news.ycombinator.com/item?id=18442941 .... Oracle indexes in mysterious ways.
😁 1
2 Views