Tag Info

Hot answers tagged

3

Try to execute the query with gather_plan_statistics hint. Then use dbms_xplan to display exec plan. You will see E-rows(estimated) and A-rows(Actual). This is where I would start, to check whether the optimizer is wrong in it's assumptions or not. Also check v$sql_plan of the running query and check whether TEMP space is really used or not. Especially on ...


3

When you use a column from a table on the "wrong"* side of an OUTER join, the join usually becomes equivalent to an INNER join. * "wrong": right side of a LEFT join and left side of a RIGHT join. So, the solution is usually to move the condition from the WHERE to the ON of the outer join. Like this: SELECT cre.id, rer.idChambre -- ...


2

Wrong interpretation of the error. You had a power failure (or some other crash). You are using MyISAM (you should use InnoDB). Those caused the "Incorrect key file for table". CHECK TABLE TBL_REPORT R; CHECK TABLE TBL_REPORT_PROXY; If either says to "repair", then perform REPAIR TABLE. If that does not work, look at this value: SHOW VARIABLES LIKE ...


1

For that many-to-many 'relation' table, don't bother to have an id. Simply have PRIMARY KEY(case_id, tag_id), INDEX(tag_id, case_id) Note that one of those works perfectly for one of your SELECTs, the other works perfectly for the other. (Use ENGINE=InnoDB.) To get (tag-1, tag-2, tag-3), use GROUP_CONCAT(). FOREIGN KEYs are optional; I would not ...


1

As far as your query goes, it seems you've followed SQL best practices so there are no easy, significant performance boosts I could find. Now, sorry to state the obvious, but make sure you have enough indexes and that you are using those indexes, and check if all your stats are up to date. Database Engine Tuning Advisor is a great tool to check both of ...


1

PROPOSED QUERY SELECT IFNULL(B.uid,'') left_uid, IFNULL(C.uid,'') right_uid FROM relation A LEFT JOIN data B ON A.left = B.id LEFT JOIN data C ON A.right = C.id ; I did it as LEFT JOIN rather than INNER JOIN in case data is missing from data table YOUR SAMPLE DATA drop database if exists gumkins; create database gumkins; use gumkins ...


1

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point: ...



Only top voted, non community-wiki answers of a minimum length are eligible